package com.nbsaas.boot.sql;

import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.select.*;

public class PageSqlHelper {

    public enum DbType {
        MYSQL, POSTGRES, SQLSERVER, ORACLE
    }

    /**
     * 构造分页SQL
     *
     * @param sql      原始SQL
     * @param pageNo   页码（从1开始）
     * @param pageSize 每页条数
     * @param dbType   数据库类型
     * @return 带分页的SQL
     */
    public static String toPageSql(String sql, int pageNo, int pageSize, DbType dbType) throws Exception {
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();

        int offsetVal = (pageNo - 1) * pageSize;

        switch (dbType) {
            case MYSQL:
            case POSTGRES:
                // LIMIT + OFFSET
                Limit limit = new Limit();
                limit.setRowCount(new net.sf.jsqlparser.expression.LongValue(pageSize));
                plainSelect.setLimit(limit);

                Offset offset = new Offset();
                offset.setOffset(new net.sf.jsqlparser.expression.LongValue(offsetVal));
                plainSelect.setOffset(offset);
                break;

            case SQLSERVER:
                // 必须有 ORDER BY
                if (plainSelect.getOrderByElements() == null || plainSelect.getOrderByElements().isEmpty()) {
                    // 如果没有 ORDER BY，则补一个 ORDER BY (SELECT NULL)
                    OrderByElement obe = new OrderByElement();
                    obe.setExpression(CCJSqlParserUtil.parseExpression("(SELECT NULL)"));
                    plainSelect.setOrderByElements(java.util.Collections.singletonList(obe));
                }

                // OFFSET
                Offset sqlServerOffset = new Offset();
                sqlServerOffset.setOffset(new net.sf.jsqlparser.expression.LongValue(offsetVal));
                plainSelect.setOffset(sqlServerOffset);

                // FETCH NEXT
                Fetch fetch = new Fetch();
                fetch.setRowCount(pageSize);
                plainSelect.setFetch(fetch);
                break;

            case ORACLE:
                // Oracle 12c 支持 FETCH NEXT … ROWS ONLY
                Fetch oracleFetch = new Fetch();
                oracleFetch.setRowCount(pageSize);
                plainSelect.setFetch(oracleFetch);

                Offset oracleOffset = new Offset();
                oracleOffset.setOffset(new net.sf.jsqlparser.expression.LongValue(offsetVal));
                plainSelect.setOffset(oracleOffset);
                break;

            default:
                throw new UnsupportedOperationException("不支持的数据库类型: " + dbType);
        }

        return select.toString();
    }

    // 测试
    public static void main(String[] args) throws Exception {
        String sql = "SELECT id, name FROM user WHERE status = 1 ORDER BY create_time DESC";

        System.out.println("MySQL分页SQL:");
        System.out.println(toPageSql(sql, 3, 10, DbType.MYSQL));

        System.out.println("\nSQL Server分页SQL:");
        System.out.println(toPageSql(sql, 3, 10, DbType.SQLSERVER));

        System.out.println("\nOracle分页SQL:");
        System.out.println(toPageSql(sql, 3, 10, DbType.ORACLE));
    }
}
